package com.audi.box.adapter;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.audi.dto.DeviceItem;
import com.audi.dto.SceneDeviceItem;
import com.audi.dto.SceneItem;

/**
 * 
 * SQLite Helper for SenceItem maintanining.
 * <p>
 * Modify By CuongPH7 - 09/09
 * 
 * @author Unkown
 */
public class SQLiteAsusHelperScene extends SQLiteAsusHelperCommon {
    
    /**
     * Class constructor specifying Context to create.
     * 
     * @param context
     *            android.content.Context
     */
    public SQLiteAsusHelperScene(final Context context) {
        super(context);
    }
    
    /**
     * Add a SceneItem.
     * 
     * @param scene
     *            SceneItem
     * @return SceneItem Id if successful, 0 if SceneItem exist
     */
    public int addScene(SceneItem scene) {
        Log.d("SQLite SceneHelper Log", "addScene");
        
        ContentValues values = convertSceneItemToContentValue(scene);
        
        int id = sqlInsert(TABLE_SCENE, null, values);
        
        return id;
    }
    
    /**
     * Update Scene Item.
     * 
     * @param scene
     *            SceneItem
     * @return 1 if successful, 0 if SceneItem does not exist
     */
    public int updateScene(SceneItem scene) {
        Log.d("SQLite SceneHelper Log", "updateScene");
        
        int vRet = 0;
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            
            ContentValues values = convertSceneItemToContentValue(scene);
            
            vRet = db.update(TABLE_SCENE, values, KEY_SCENE_ID + " = ? ",
                    new String[] {String.valueOf(scene.getSceneId())});
            
        } catch (SQLException e) {
            vRet = 0;
        } finally {
            if (db != null) {
                db.close();
            }
        }
        
        return vRet;
        
    }
    
    /**
     * Delete a SceneItem.
     * 
     * @param itemScene
     * @return 1 if successful, 0 if SceneItem does not exist
     */
    public int deleteSceneById(SceneItem scene) {
        Log.d("SQLite SceneHelper Log", "deleteSceneById");
        
        ContentValues values = new ContentValues();
        values.put(KEY_SCENE_IS_DELETED, 1);
        
        int id = sqlUpdate(TABLE_SCENE, values,
                KEY_SCENE_ID + "=" + String.valueOf(scene.getSceneId()), null);
        
        return id;
    }
    
    /**
     * Get SceneItem By Scene Id.
     * 
     * @author CuongPH7
     * @param sceneId
     * @return SceneItem
     */
    public SceneItem getSceneById(int sceneId) {
        Log.d("SQLite SceneHelper Log", "getSceneById");
        
        SceneItem scene = new SceneItem();
        
        String selectQuery = "SELECT * FROM " + TABLE_SCENE + " WHERE "
                + KEY_SCENE_ID + " = '" + sceneId + "' AND "
                + KEY_SCENE_IS_DELETED + " != 1";
        
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
                scene = createSceneItem(cursor);
            }
            db.close();
            
        } catch (Exception e) {
            
        }
        return scene;
    }
    
    public ArrayList<SceneItem> getSceneByPlaceId(int placeId) {
        Log.d("SQLite SceneHelper Log", "getSceneByPlaceId");
        ArrayList<SceneItem> listScene = new ArrayList<SceneItem>();
        
        String selectQuery = "SELECT " + TABLE_SCENE + "." + KEY_SCENE_ID
                + ", " + TABLE_SCENE + "." + KEY_SCENE_NAME + ", "
                + TABLE_SCENE + "." + KEY_SCENE_ICON + ", " + TABLE_SCENE + "."
                + KEY_SCENE_COLOR + ", " + TABLE_SCENE + "."
                + KEY_SCENE_PLACE_ID + ", " + TABLE_PLACE + "."
                + KEY_PLACE_NAME;
        
        selectQuery += " FROM " + TABLE_SCENE + " LEFT OUTER JOIN "
                + TABLE_PLACE + " ON " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID
                + " = " + TABLE_PLACE + "." + KEY_PLACE_ID;
        selectQuery += " WHERE " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID + "="
                + placeId;
        selectQuery += " AND " + TABLE_SCENE + "." + KEY_SCENE_IS_DELETED
                + " = " + 0;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    
                    int columnIndex = 0;
                    SceneItem scene = createSceneItem(cursor);
                    
                    columnIndex = cursor.getColumnIndexOrThrow(KEY_PLACE_NAME);
                    if (!cursor.isNull(columnIndex)) {
                        scene.setScenePlace(cursor.getString(columnIndex));
                    }
                    
                    listScene.add(scene);
                    
                } while (cursor.moveToNext());
            }
            return listScene;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /*
     * GET LIST ALL PLACE
     */
    public ArrayList<SceneItem> getListScene() {
        Log.d("SQLite SceneHelper Log", "getListScene");
        ArrayList<SceneItem> listScene = new ArrayList<SceneItem>();
        String selectQuery = " SELECT * ," + TABLE_PLACE + "." + KEY_PLACE_NAME
                + " FROM " + TABLE_SCENE + ", " + TABLE_PLACE + " WHERE "
                + KEY_SCENE_IS_DELETED + " = 0 and " + TABLE_SCENE + "."
                + KEY_SCENE_PLACE_ID + "=" + TABLE_PLACE + "." + KEY_PLACE_ID
                
                + " ORDER BY " + KEY_SCENE_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    
                    SceneItem scene = createSceneItem(cursor);
                    
                    int columnIndex = cursor.getColumnIndexOrThrow(KEY_PLACE_NAME);
                    if (!cursor.isNull(columnIndex)) {
                        scene.setScenePlace(cursor.getString(columnIndex));
                    }
                    
                    listScene.add(scene);
                    
                } while (cursor.moveToNext());
            }
            
        } catch (Exception e) {
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
        return listScene;
    }
    
    /*
     * Check exist scene
     */
    public boolean checkExistScene(SceneItem itemScene) {
        Log.d("SQLite SceneHelper Log", "checkExistScene");
        String selectQuery = "SELECT " + KEY_SCENE_ID + " FROM " + TABLE_SCENE
                + " WHERE " + KEY_SCENE_ID + "='" + itemScene.getSceneId()
                + "'" + " AND " + KEY_SCENE_IS_DELETED + " = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /*
     * Check exist scene name
     */
    public boolean checkExistSceneByName(SceneItem itemScene) {
        Log.d("SQLite SceneHelper Log", "checkExistScene");
        String selectQuery = "SELECT " + KEY_SCENE_ID + " FROM " + TABLE_SCENE
                + " WHERE " + KEY_SCENE_NAME + "='" + itemScene.getSceneTitle()
                + "'" + " AND " + KEY_SCENE_IS_DELETED + " = 0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Add a SceneDeviceItem.
     * 
     * @param sceneDevice
     *            SceneDeviceItem
     * @return SceneDeviceItem Id if successful, 0 if (SceneDeviceItem exist
     */
    public int addSceneDevice(SceneDeviceItem sceneDevice) {
        Log.d("SQLite SceneHelper Log", "addSceneDevice");
        
        ContentValues values = convertSceneDeviceItemToContentValue(sceneDevice);
        
        int id = sqlInsert(TABLE_SCENE_DEVICE, null, values);
        
        return id;
    }
    
    /**
     * Update Scene Device Item
     * 
     * @param sceneDevice
     *            SceneDeviceItem
     * @return 1 if successful, 0 if SceneItem does not exist
     */
    public int updateSceneDevie(SceneDeviceItem sceneDevice) {
        Log.d("SQLite SceneHelper Log", "updateScene");
        
        int vRet = 0;
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            
            ContentValues values = convertSceneDeviceItemToContentValue(sceneDevice);
            
            vRet = db
                    .update(TABLE_SCENE_DEVICE,
                            values,
                            KEY_SCENE_DEVICE_SCENE_ID + " = ? AND "
                                    + KEY_SCENE_DEVICE_DEVICE_ID + " = ? ",
                            new String[] {
                                    String.valueOf(sceneDevice.getScene()
                                            .getSceneId()),
                                    String.valueOf(sceneDevice.getDevice()
                                            .getDeviceId())});
            
        } catch (SQLException e) {
            vRet = 0;
        } finally {
            if (db != null) {
                db.close();
            }
        }
        
        return vRet;
        
    }
    
    /**
     * Get SceneDeviceItem.
     * 
     * @author CuongPH7
     * @param sceneId
     *            Scene Id
     * @param deviceId
     *            Device Id
     * @return a SceneDeviceItem
     */
    public SceneDeviceItem getSceneDevice(int sceneId, int deviceId) {
        ArrayList<SceneDeviceItem> lstSceneDevice = getSceneDeviceList(sceneId,
                deviceId);
        if (lstSceneDevice != null && lstSceneDevice.size() > 0) {
            return lstSceneDevice.get(0);
        } else {
            return new SceneDeviceItem();
        }
    }
    
    /**
     * Get SceneDeviceItem List.
     * 
     * @author CuongPH7
     * @return an ArrayList<SceneDeviceItem>
     */
    public ArrayList<SceneDeviceItem> getSceneDeviceList() {
        return getSceneDeviceList(0, 0);
    }
    
    /**
     * Get SceneDeviceItem List.
     * 
     * @author CuongPH7
     * @param sceneId
     *            Scene Id
     * @param deviceId
     *            Device Id
     * @return an ArrayList<SceneDeviceItem>
     */
    public ArrayList<SceneDeviceItem> getSceneDeviceList(int sceneId,
            int deviceId) {
        ArrayList<SceneDeviceItem> lstSceneDevice = new ArrayList<SceneDeviceItem>();
        String selectQuery = " SELECT "
                // GET SCENE-DEVICE INFO
                + TABLE_SCENE_DEVICE
                + "."
                + KEY_SCENE_DEVICE_SCENE_ID
                + ", "
                + TABLE_SCENE_DEVICE
                + "."
                + KEY_SCENE_DEVICE_DEVICE_ID
                + ","
                + TABLE_SCENE_DEVICE
                + "."
                + KEY_SCENE_DEVICE_DEVICE_STATUS
                + ", "
                
                // GET SCENE-DEVICE SCENE INFO
                + TABLE_SCENE
                + "."
                + KEY_SCENE_NAME
                + ", "
                + TABLE_SCENE
                + "."
                + KEY_SCENE_COLOR
                + ", "
                + TABLE_SCENE
                + "."
                + KEY_SCENE_ICON
                + ", "
                + TABLE_SCENE
                + "."
                + KEY_SCENE_PLACE_ID
                + ", "
                // GET PLACE NAME FOR SCENE PLACE
                + "TABLE_PLACE_A."
                + KEY_PLACE_NAME
                + " AS SCENE_PLACE_NAME, "
                
                // GET SCENE-DEVICE DEVICE INFO
                + TABLE_DEVICE
                + "."
                + KEY_DEVICE_NAME
                + ", "
                + TABLE_DEVICE
                + "."
                + KEY_DEVICE_ICON
                + ", "
                + TABLE_DEVICE
                + "."
                + KEY_DEVICE_TYPE
                + ", "
                + TABLE_DEVICE
                + "."
                + KEY_DEVICE_PLACE_ID
                + ", "
                // GET PLACE NAME FOR SCENE PLACE
                + "TABLE_PLACE_B."
                + KEY_PLACE_NAME
                + " AS DEVICE_PLACE_NAME, "
                
                // TABLE TABLE_SCENE_DEVICE
                + " FROM "
                + TABLE_SCENE_DEVICE
                
                // JOIN TABLE_SCENE
                + " LEFT JOIN " + TABLE_SCENE + " ON "
                + TABLE_SCENE_DEVICE
                + "."
                + KEY_SCENE_DEVICE_SCENE_ID
                + "="
                + TABLE_SCENE
                + "."
                + KEY_SCENE_ID
                
                // JOIN TABLE_DEVICE
                + " LEFT JOIN " + TABLE_DEVICE + " ON " + TABLE_SCENE_DEVICE
                + "." + KEY_SCENE_DEVICE_DEVICE_ID
                + "="
                + TABLE_DEVICE
                + "."
                + KEY_DEVICE_ID
                
                // JOIN TABLE_PLACE FOR SCENE PLACE NAME
                + " LEFT JOIN " + TABLE_PLACE + " AS TABLE_PLACE_A ON "
                + "TABLE_PLACE_A." + KEY_PLACE_ID + "=" + TABLE_SCENE
                + "."
                + KEY_SCENE_PLACE_ID
                
                // JOIN TABLE_PLACE FOR DEVICE PLACE NAME
                + " LEFT JOIN " + TABLE_PLACE + " AS TABLE_PLACE_B ON "
                + "TABLE_PLACE_B." + KEY_PLACE_ID + "=" + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID;
        
        ArrayList<String> where = new ArrayList<String>();
        
        if (sceneId != 0) {
            where.add(TABLE_SCENE_DEVICE + "." + KEY_SCENE_DEVICE_SCENE_ID
                    + "=" + sceneId);
            where.add(TABLE_SCENE + "." + KEY_SCENE_IS_DELETED + "=0");
        }
        
        if (deviceId != 0) {
            where.add(TABLE_SCENE_DEVICE + "." + KEY_SCENE_DEVICE_DEVICE_ID
                    + "=" + deviceId);
            where.add(TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED + "=0");
        }
        
        where.add(TABLE_SCENE_DEVICE + "." + KEY_SCENE_DEVICE_IS_DELETED + "=0");
        
        selectQuery += " WHERE " + TextUtils.join(" AND ", where);
        
        selectQuery += " GROUP BY " + TABLE_SCENE_DEVICE + "."
                + KEY_SCENE_DEVICE_SCENE_ID + ", " + TABLE_SCENE_DEVICE + "."
                + KEY_SCENE_DEVICE_DEVICE_ID;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                SceneItem scene = createSceneItem(cursor);
                DeviceItem device = createDeviceItem(cursor);
                
                SceneDeviceItem sceneDevice = createSceneDeviceItem(cursor);
                sceneDevice.setScene(scene);
                sceneDevice.setDevice(device);
                
                lstSceneDevice.add(sceneDevice);
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
        return lstSceneDevice;
    }
    
    /**
     * Delete a Scene Device Item.
     * 
     * @param sceneDevice
     *            SceneDeviceItem
     * @return 1 if successful, 0 if SceneDeviceItem does not exist
     */
    public int deleteSceneDeviceItem(SceneDeviceItem sceneDevice) {
        Log.d("SQLite SceneHelper Log",
                "deleteSceneDeviceItem(SceneDeviceItem sceneDevice)");
        
        ContentValues values = new ContentValues();
        values.put(KEY_SCENE_DEVICE_IS_DELETED, 1);
        
        int id = sqlUpdate(
                TABLE_SCENE_DEVICE,
                values,
                KEY_SCENE_DEVICE_SCENE_ID + "="
                        + String.valueOf(sceneDevice.getScene().getSceneId())
                        + KEY_SCENE_DEVICE_DEVICE_ID + "="
                        + String.valueOf(sceneDevice.getDevice().getDeviceId()),
                null);
        
        return id;
    }
    
}
